import json
import sqlite3
from typing import List, Any


class LocalCache:
    def __init__(self, db_file="D:\\project\\python\\test-tools\\app\\testTools.db"):
        self.db_file = db_file
        # if os.path.exists(self.db_file):
        self.conn = sqlite3.connect(db_file)
        self.cursor = self.conn.cursor()
        self.create_table()

    def create_table(self):
        """
        创建airportInformation和runwayInformation两张表
        :param self:
        :return:
        """
        try:
            self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS airportInformation
                        (
                            four_code TEXT PRIMARY KEY,
                            alt       TEXT,
                            name      TEXT
                            
                        );
            """
            )
            self.cursor.execute(
                """
                create table if not exists runwayInformation
                        (
                            id                 INTEGER PRIMARY KEY,
                            runway             TEXT,
                            rwyName            TEXT,
                            entranceLongitude  TEXT,
                            entranceLatitude   TEXT,
                            airportInformation TEXT,
                            UNIQUE(rwyName, airportInformation)
                        );
            """
            )
            self.conn.commit()
            return True, "create table success"
        except Exception as e:
            return False, str(e)

    def write_data(self, key, value):
        try:
            # 将数据转换为 JSON 字符串存储
            serialized_value = json.dumps(value)
            self.cursor.execute(
                "INSERT OR REPLACE INTO cached_data (key, value) VALUES (?, ?)",
                (key, serialized_value),
            )
            self.conn.commit()
            print(f"数据 '{key}' 写入成功.")
        except Exception as e:
            print(f"写入数据 '{key}' 出错:", str(e))

    def create_or_update_airport_information(self, four_code, alt, name):
        try:
            self.cursor.execute(
                "INSERT OR REPLACE INTO airportInformation (four_code, alt, name) VALUES (?, ?, ?)",
                (four_code, alt, name),
            )
            self.conn.commit()
            return True, "success"
        except Exception as e:
            return False, e

    def create_or_update_runway_information(
        self, runway, name, entrance_longitude, entrance_latitude, airport_information
    ):
        """

        :param runway:
        :param name:
        :param entrance_longitude:
        :param entrance_latitude:
        :param airport_information:
        :return:
        """
        try:
            self.cursor.execute(
                "INSERT OR REPLACE INTO runwayInformation (runway, rwyName, entranceLongitude, "
                "entranceLatitude, airportInformation) VALUES (?,?,?,?,?)",
                (
                    runway,
                    name,
                    entrance_longitude,
                    entrance_latitude,
                    airport_information,
                ),
            )
            self.conn.commit()
            return True, "success"
        except Exception as e:
            return False, e

    def get_rwy_info(self, four_code=None):
        """

        :param four_code: 四字码
        :return:
        """
        try:
            if four_code:
                self.cursor.execute(
                    """
                    SELECT *
                    FROM airportInformation
                    INNER JOIN runwayInformation ON airportInformation.four_code = runwayInformation.airportInformation
                    WHERE airportInformation.four_code = ?
                """,
                    (four_code,),
                )
            else:
                self.cursor.execute(
                    """
                        SELECT *
                            FROM airportInformation
                            INNER JOIN runwayInformation ON airportInformation.four_code = runwayInformation.airportInformation
                """
                )

        except Exception as e:
            print(f"查询数据出错1:", str(e))
            return None
        finally:
            try:
                # 获取查询结果的列名

                # 返回查询结果
                return self.result_to_list()
            except Exception as e:
                print(f"获取查询结果出错2:", str(e))

    def get_airport(self, four_codes=None):
        """

        :param four_codes: 四字码
        :return:
        """
        print(self.db_file)
        try:
            if four_codes:
                self.cursor.execute(
                    """
                    SELECT *
                    FROM airportInformation
                    WHERE four_code IN (?)
                """,
                    (four_codes,),
                )
            else:
                self.cursor.execute(
                    """
                        SELECT *
                            FROM airportInformation
                """
                )

        except Exception as e:
            print(f"查询数据出错1:", str(e))
            return []
        finally:
            try:
                return self.result_to_list()
            except Exception as e:
                print(f"获取查询结果出错2:", str(e))
                return []

    def close_connection(self):
        self.cursor.close()
        self.conn.close()
        return True, "close connection"

    def result_to_list(self):
        """
        查询结果转为list
        :return:
        """
        columns = [desc[0] for desc in self.cursor.description]
        rows = self.cursor.fetchall()
        if not rows:
            return []
        __result = [dict(zip(columns, row)) for row in rows]
        return __result


# 使用示例
if __name__ == "__main__":
    cache = LocalCache()

    print(cache.get_airport())
    print(cache.get_rwy_info())
    #
    # # 写入数据示例
    # __, result = cache.create_table()
    # print(result)
    # if __:
    #     cache.create_or_update_airport_information("ZUCK", "417", "重庆")
    #     cache.create_or_update_runway_information(
    #         "123", "Runway 1", "116.63", "39.89", "ZUCK"
    #     )
    #     cache.create_or_update_runway_information(
    #         "123", "Runway 2", "3343", "33", airport_information="ZUCK"
    #     )
    # _t = cache.get_airport()
    # print(_t)
    # # 读取数据示例
    # data = cache.get_airport("ZUUU")
    # if data:
    #     print("读取到的数据:", data)
    #
    # data2 = cache.get_rwy_info("ZUUU")
    # if data2:
    #     print("读取到的数据:", data2)
    #
    # data3 = cache.get_rwy_info()
    # if data3:
    #     print("读取到的数据:", data3)
    # # 关闭数据库连接
    # cache.close_connection()
